import pathlib
import threading
import time
import xml.etree.ElementTree as ET
import sys
import os
from pathlib import Path
# from PIL.ImageChops import darker
from PyQt5.QtWidgets import QApplication, QMainWindow, QPushButton, QVBoxLayout, QWidget, QTextEdit, QMessageBox, \
    QHBoxLayout, QLabel, QLineEdit, QFileDialog, QDialog
from PyQt5.QtCore import QThread, pyqtSignal
# from Ui_ReToExcel import Ui_reportSummary
from Ui_all import Ui_reportSummary
# from Ui_all_EnglishOperation import Ui_reportSummary
import openpyxl  # 处理Excel
import concurrent.futures
import logging
from ReadS118 import S118ConvertWorker
from OP70 import OP70ConvertWorker
from Fallback import OP20ConvertWorker
from qtmodern.styles import dark, light


# logging.basicConfig(filename='running log.txt', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')


# Txt to Excel
class ConvertWorker(QThread):
    statusMessage = pyqtSignal(str)
    finish_signal = pyqtSignal(str)

    def __init__(self, txt_files_path, example_file):
        super().__init__()
        self.txt_files_path = txt_files_path
        self.example_file = example_file
        self.finish_signal = False

    def get_txt_file_paths(self):
        try:
            return [os.path.join(self.txt_files_path, file) for file in os.listdir(self.txt_files_path) if
                    file.endswith('.txt')]
        except Exception as e:
            print(f"Error getting txt file paths: {e}")
            return []

    def get_test_name(self, file_path):
        try:
            with open(file_path, 'r') as file:
                lines = file.readlines()
                test_names = []
                found_testname = False
                for line in lines:
                    if line.startswith('[Testname]'):
                        found_testname = True
                    elif found_testname:
                        if len(line) >= 31:
                            test_names.append(line[:31].strip())
                return test_names
        except Exception as e:
            print(f"Error in get_test_name for file {file_path}: {e}")
            return []

    def insert_limit(self, file_path):
        try:
            result_list = []
            test_names = self.get_test_name(file_path)
            for name in test_names:
                result_list.extend([name, 'Min', 'Result', 'Max'])
            return result_list
        except Exception as e:
            print(f"Error in insert_limit for file {file_path}: {e}")
            return []

    def get_testplan_number(self, string_list):
        try:
            if 'FT1' in string_list:
                return 'FT1'
            elif 'FT2' in string_list:
                return 'FT2'

            else:
                return 'xx'
        except Exception as e:
            print(f"Error in get_testplan_number for file {string_list}: {e}")
            return []

    def process_lines(self, lines):
        test_data = []
        for line in lines[21:]:
            parts = line.split()
            if len(parts) < 4:
                continue
            index = None
            if 'passed' in parts:
                index = parts.index('passed')
            elif 'failed' in parts:
                index = parts.index('failed')
            if 'TD>30' in parts:
                index_td = parts.index('TD>30')
                parts.pop(index_td)
            if index is not None:
                state = parts[index]
                min_val = parts[index + 1]
                if 'DEC"' in parts:
                    max_val = parts[index + 5]
                    result = parts[index + 3]
                else:
                    max_val = parts[index + 3]
                    result = parts[index + 2]
                test_data.extend([state, min_val, result, max_val])
        return test_data

    def run(self):
        try:
            # 假设这里的 statusMessage.emit 是自定义的方法，用来发送状态消息
            self.statusMessage.emit('Starting conversion...')
            txt_files = self.get_txt_file_paths()
            self.statusMessage.emit('Reading paths...')
            self.name_lst = self.get_test_name(
                self.example_file) if txt_files else []
            column_names = ['Serial number', 'Module State', 'Date_Time', 'Testplan_number'] + self.insert_limit(
                self.example_file) if txt_files else []
            wb = openpyxl.Workbook()
            ws = wb.active
            ws.title = "PASS_Data"
            ws1 = wb.create_sheet('FailData')
            # 给sheet页面添加title
            for col_idx, col_name in enumerate(column_names, start=1):
                ws.cell(row=1, column=col_idx, value=col_name)
                ws1.cell(row=1, column=col_idx, value=col_name)
            # 多个txt文件读取，编列每个txt的内容
            for i, file in enumerate(txt_files):
                self.statusMessage.emit(f'Reading file {i + 1}.')
                with open(file, 'r') as f:
                    lines = f.readlines()  # 读取返回列表
                    serial_number = lines[3].split()[1]  # 零件号
                    module_state = lines[15].split()[3]  # 测试状态
                    Testplan_number = self.get_testplan_number(lines[6].split()[1])  # FT号
                    Date_Time = lines[16].split()[1] + ' ' + lines[17].split()[1]  # 测试的日期
                    test_dict = create_test_dict(self.name_lst)  # 将测试名称创建字典
                    fill_test_dict(file, test_dict)  # 处理数据，将所需要的数据保存在字典
                    # 初始化 row_data 为固定长度的空列表
                    # 将添加到列表
                    row_data = [None] * len(column_names)
                    row_data[0] = serial_number
                    row_data[1] = module_state
                    row_data[2] = Date_Time
                    row_data[3] = Testplan_number
                    # 采用一对一的形式添加到row_data列表中 ，首先遍历含有测试用例名称的字典 ，如果这个测试名称在title里面，则将对应的index返回，按照index的位置顺序添加txt中的测试数据
                    for item in test_dict:  # 编列字典
                        if item in column_names:  # 判断字典中的testname是否在column中
                            index = column_names.index(item)
                            test_info = test_dict[item]
                            row_data.insert(index, test_info['states'])
                            row_data.insert(index + 1, test_info['Min'])
                            row_data.insert(index + 2, test_info['Result'])
                            row_data.insert(index + 3, test_info['Max'])
                        else:
                            pass
                    # 分开俩个sheet页面
                    if module_state == 'FAIL':
                        ws1.append(row_data)

                    elif module_state == 'PASS':
                        ws.append(row_data)

            excel_name = f'Data{time.strftime("%Y%m%d%H%M%S")}.xlsx'
            save_path = Path(self.txt_files_path) / excel_name
            wb.save(save_path)
            time.sleep(1)
            # 启动检查excel中fail项的程序，
            self.format_thread = FormatExcelThread(os.path.join(self.txt_files_path, excel_name))
            self.format_thread.statusMessage.connect(self.statusMessage)

            self.format_thread.run()
            self.wait()
            self.statusMessage.emit("Conversion completed!")
            self.statusMessage.emit(f"Would save here:{self.txt_files_path}")

        except Exception as e:
            self.statusMessage.emit(f"Conversion failed: {str(e)}")


class FormatExcelThread(QThread):
    statusMessage = pyqtSignal(str)

    def __init__(self, file_path):
        super().__init__()
        self.file_path = file_path

    def run(self):
        try:
            # mark_failed_cells(self.file_path)
            workbook = openpyxl.load_workbook(self.file_path)
            sheet = workbook.get_sheet_by_name('FailData')

            yellow_fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            red_font = openpyxl.styles.Font(color='FF0000')
            default_font = openpyxl.styles.Font(color='000000')
            for row in sheet.iter_rows():
                for cell in row:
                    if str(cell.value).lower() == 'failed' or str(cell.value).lower() == 'fail' or str(
                            cell.value).lower() == 'FAIL':
                        cell.fill = yellow_fill
                        cell.font = red_font
                    else:
                        cell.font = default_font

            workbook.save(self.file_path)
        except Exception as e:
            print(f"Error formatting excel: {e}")
            self.statusMessage.emit(f"File inspection failed: {str(e)}")


class MainWindow(QMainWindow, Ui_reportSummary):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        # 设置透明，背景炫酷黑颜色，字体白色
        # self.init_Ui()
        self.selectFolderButton = self.pushButton
        self.convertButton = self.pushButton_3
        self.selectFolderButton.clicked.connect(self.open_path)
        self.convertButton.clicked.connect(self.start_conversion)
        self.pushButton_2.clicked.connect(self.openExampleFile)
        self.pushButton_4.clicked.connect(self.open_xml_floder)
        self.pushButton_5.clicked.connect(self.start_xml_report)
        self.pushButton_6.clicked.connect(self.open_xml_example)
        self.pushButton_7.clicked.connect(self.open_S56_report_folder)
        self.pushButton_8.clicked.connect(self.Start_S56_report)
        self.pushButton_10.clicked.connect(self.open_S118_report_folder)
        self.pushButton_9.clicked.connect(self.Start_S118_report)
        self.pushButton_12.clicked.connect(self.open_OP70_report_folder)
        self.pushButton_11.clicked.connect(self.Start_OP70_report)
        # self.pushButton_15.clicked.connect(self.open_OP70_template_file)
        self.pushButton_14.clicked.connect(self.open_OP20_report_folder)
        self.pushButton_13.clicked.connect(self.Start_OP20_report)

        self.flag = 0
        # 指定要显示的字母
        letter = 'VT'
        # 创建包含指定字母的图标
        icon = create_icon_with_letter(letter)
        # 设置窗口图标
        self.setWindowIcon(icon)


    def init_Ui(self):
        self.setWindowOpacity(0.7)
        self.setStyleSheet("background-color:  #000000; color: blue;")
        # 设置tag 里面字体白色色
        self.tab.setStyleSheet("color: white;")
        self.tab_2.setStyleSheet("color: white;")
        self.S56.setStyleSheet("color: white;")
        self.tab_3.setStyleSheet("color: white;")
        self.tab_4.setStyleSheet("color: white;")
        self.tab_5.setStyleSheet("color: white;")
        self.textBrowser.setStyleSheet("background-color: #000000; color: white;")
        # 设置按钮背景蓝颜色
        self.pushButton.setStyleSheet("background-color: blue;")
        self.pushButton_2.setStyleSheet("background-color: blue;")
        self.pushButton_3.setStyleSheet("background-color: blue;")
        self.pushButton_4.setStyleSheet("background-color: blue;")
        self.pushButton_5.setStyleSheet("background-color: blue;")
        self.pushButton_6.setStyleSheet("background-color: blue;")
        self.pushButton_7.setStyleSheet("background-color: blue;")
        self.pushButton_8.setStyleSheet("background-color: blue;")
        self.pushButton_10.setStyleSheet("background-color: blue;")
        self.pushButton_9.setStyleSheet("background-color: blue;")
        self.pushButton_11.setStyleSheet("background-color: blue;")
        self.pushButton_12.setStyleSheet("background-color: blue;")
        self.pushButton_13.setStyleSheet("background-color: blue;")
        self.pushButton_14.setStyleSheet("background-color: blue;")
        # self.pushButton_15.setStyleSheet("background-color: blue;")

    def open_xml_floder(self):
        xml_path = QFileDialog.getExistingDirectory(self, "Select XML folder")
        self.lineEdit_3.setText(xml_path)

    def open_xml_example(self):
        xml_example = QFileDialog.getOpenFileName(self, "Select XML example file", "", "XML Files (*.xml)")[0]
        self.lineEdit_4.setText(xml_example)

    def start_xml_report(self):
        self.flag = 2
        xml_path = self.lineEdit_3.text()
        if xml_path and self.lineEdit_4.text():
            xml_example = self.lineEdit_4.text()
            try:

                self.textBrowser.clear()
                self.xml_report = XmlConvertWorker(xml_path, xml_example)
                self.xml_report.statusMessage.connect(self.display_message)

                self.xml_report.start()

            except Exception as e:
                self.display_message(f"创建 XmlConvertWorker failed：{str(e)}")

        else:

            self.display_message("Please select XML folder and sample file！")

    def openExampleFile(self):
        global exampleFile
        exampleFile = QFileDialog.getOpenFileName(self, "Select example file", "", "TXT Files (*.txt)")[0]
        self.textBrowser.append(f"Selected example file: {exampleFile}")
        self.lineEdit_2.setText(exampleFile)

    def open_path(self):
        txt_files_path = QFileDialog.getExistingDirectory(self, "Select TXT folder")
        self.lineEdit.setText(txt_files_path)

    def start_conversion(self):
        self.flag = 1
        try:
            if self.lineEdit.text() and self.lineEdit_2.text():
                self.textBrowser.clear()

                txt_files_path = self.lineEdit.text()
                self.convert_worker = ConvertWorker(txt_files_path, self.lineEdit_2.text())
                self.convert_worker.statusMessage.connect(self.display_message)
                self.convert_worker.start()
            else:
                if not self.lineEdit.text():
                    self.textBrowser.append("Please select Report folder path!")
                if not self.lineEdit_2.text():
                    self.textBrowser.append("Please select sample Txt path!")
        except Exception as e:
            self.textBrowser.append(f"Conversion failed: {str(e)}")
            QMessageBox.setStyleSheet(self, "QLabel{color: white;")
            QMessageBox.warning(self, "Warning", f"Conversion failed: {str(e)}")
            # self.init_Ui()

    def display_message(self, message):
        try:
            self.textBrowser.append(message)
            if message == "Conversion completed!" or message == "转换完成":
                # 设置QMessageBox内的字体颜色为白色
                QMessageBox.setStyleSheet(self, "QLabel{color: white;")
                QMessageBox.information(self, "Info", "Conversion completed!")
                # self.init_Ui()

        except Exception as e:
            print(f"Error displaying message: {e}")

    def open_S56_report_folder(self):
        try:
            S56_report_path = QFileDialog.getExistingDirectory(self, "Select S56 report folder")
            self.lineEdit_5.setText(S56_report_path)
        except Exception as e:
            print(f"Error opening folder: {e}")

    def Start_S56_report(self):
        self.flag = 3
        S56_report_path = self.lineEdit_5.text()
        try:
            if S56_report_path:
                self.textBrowser.clear()
                self.S56_report = S56ReportWorker(S56_report_path)
                self.S56_report.statusMessage.connect(self.display_message)
                self.S56_report.start()
            else:
                self.display_message("Please select S56 report folder path!")

        except Exception as e:
            self.display_message(str(e))

    def open_S118_report_folder(self):
        S118_report_path = QFileDialog.getExistingDirectory(self, "Select S118 report folder")
        self.lineEdit_6.setText(S118_report_path)

    def Start_S118_report(self):
        try:
            S118_report_path = self.lineEdit_6.text()
            if S118_report_path:
                self.textBrowser.clear()

                self.S118_report = S118ConvertWorker(S118_report_path)
                self.S118_report.statusMessage.connect(self.display_message)
                self.S118_report.start()
            else:
                self.display_message("Please select S118 report folder path!")
                # self.init_Ui()

        except Exception as e:
            self.display_message(str(e))

    def open_OP70_report_folder(self):
        OP70_report_path = QFileDialog.getExistingDirectory(self, "Select OP70 report folder")
        self.lineEdit_7.setText(OP70_report_path)

    def Start_OP70_report(self):
        try:
            OP70_report_path = self.lineEdit_7.text()
            # OP70_template_path = self.lineEdit_9.text()
            if OP70_report_path:
                logging.info(f"OP70_report_path: {OP70_report_path}")
                print(f"OP70_report_path: {OP70_report_path}")
                self.textBrowser.clear()
                self.OP70_report = OP70ConvertWorker(OP70_report_path)

                self.OP70_report.statusMessage.connect(self.display_message)
                self.OP70_report.start()

            else:
                self.display_message("Please select OP70 report folder path ! ")

        except Exception as e:
            self.display_message(str(e))
            # self.init_Ui()
            QMessageBox.setStyleSheet(self, "QLabel{color: white;")

    def open_OP20_report_folder(self):
        OP20_report_path = QFileDialog.getExistingDirectory(self, "Select OP20 report folder")
        self.lineEdit_8.setText(OP20_report_path)

    def Start_OP20_report(self):
        try:
            OP20_report_path = self.lineEdit_8.text()

            if OP20_report_path:

                self.textBrowser.clear()
                self.OP20_report = OP20ConvertWorker(OP20_report_path)
                self.OP20_report.statusMessage.connect(self.display_message)
                self.OP20_report.start()

            else:
                self.display_message("Please select OP21 report folder path ! ")


        except Exception as e:

            self.display_message(str(e))

    # def open_OP70_template_file(self):
    #     OP70_template_path = QFileDialog.getOpenFileName(self, "Select OP70 template file", "", "Excel Files (*.xlsx)")[0]
    #     self.lineEdit_9.setText(OP70_template_path)


class XmlConvertWorker(QThread):
    statusMessage = pyqtSignal(str)

    def __init__(self, xml_files_path, example_file):
        super().__init__()
        self.xml_files_path = xml_files_path
        self.example_file = example_file

    def get_txt_file_paths(self):
        try:
            return [os.path.join(self.xml_files_path, file) for file in os.listdir(self.xml_files_path) if
                    file.endswith('.xml')]
        except Exception as e:
            print(f"Error getting xml file paths: {e}")
            return []

    def run(self):

        try:
            self.statusMessage.emit('Starting conversion...')
            txt_files = self.get_txt_file_paths()
            self.statusMessage.emit('Reading paths...')
            read_xml = GetXmlData(self.example_file)
            column_names = ['Serial number', 'Status', 'Date_Time', 'Testplan_number'] + read_xml.add_limit_data()
            test = 0
            wb = openpyxl.Workbook()
            ws = wb.active
            ws1 = wb.create_sheet('FailData')
            ws.title = "PassData"

            for col_idx, col_name in enumerate(column_names, start=1):
                ws.cell(row=1, column=col_idx, value=col_name)
                ws1.cell(row=1, column=col_idx, value=col_name)
            self.statusMessage.emit(f'this length is {len(txt_files)} ')
            #  遍历文件夹内的xml数据文件
            for i, file in enumerate(txt_files):
                read_xml_ = GetXmlData(file)
                serial_number = read_xml_.get_serial_number()  # 零件号
                Date_Time = getWordInString('T', read_xml_.get_date_time())  # 去掉日期中的T 字符
                Testplan_number = read_xml_.get_testplan_number()  # FT编号
                self.statusMessage.emit(f'Reading {serial_number}')
                test_data = read_xml_.get_min_result_max_data()  #

                self.statusMessage.emit('Reading data...')
                self.statusMessage.emit(f'Reading file {i + 1}.')
                test_dict = create_test_dict(read_xml_.get_test_name_data())  # 将测试名称创建字典
                read_xml_.dict_to_data(test_dict)  # 处理数据，将所需要的数据保存在字典
                # 初始化 row_data 为固定长度的空列表
                # 将添加到列表
                row_data = [None] * len(column_names)
                row_data[0] = serial_number
                row_data[1] = read_xml_.get_status()
                row_data[2] = Date_Time
                row_data[3] = Testplan_number
                # 采用一对一的形式添加到row_data列表中 ，首先遍历含有测试用例名称的字典 ，如果这个测试名称在title里面，则将对应的index返回，按照index的位置顺序添加txt中的测试数据
                for item in test_dict:  # 编列字典
                    if item in column_names:  # 判断字典中的testname是否在column中
                        index = column_names.index(item)
                        test_info = test_dict[item]
                        row_data.insert(index, test_info['states'])
                        row_data.insert(index + 1, test_info['Min'])
                        row_data.insert(index + 2, test_info['Result'])
                        row_data.insert(index + 3, test_info['Max'])
                    else:
                        pass

                # 判断是不是完整的测试报告
                if not test_data:
                    data_row = [serial_number]
                else:
                    status = read_xml_.get_status()
                    data_row = [serial_number, status, Date_Time, Testplan_number] + test_data
                    # 分开俩个sheet页面
                    if status == 'Fail':
                        ws1.append(row_data)
                        row_data = []
                    elif status == 'Pass':
                        ws.append(row_data)
                        row_data = []
                # ws.append(data_row)
            self.statusMessage.emit("Data reading completed. Waiting for subsequent data processing!")
            excel_name = f'xmlToData{time.strftime("%Y%m%d%H%M%S")}.xlsx'
            wb.save(os.path.join(self.xml_files_path, excel_name))
            self.statusMessage.emit("Checking fail items in the file!")
            self.for_thread = FormatXmlExcelThread(os.path.join(self.xml_files_path, excel_name))
            self.for_thread.statusMessage.connect(self.statusMessage)
            self.for_thread.run()
            self.for_thread.start()
            # mark_failed_cells(os.path.join(self.xml_files_path, 'xmlToData.xlsx'))
            self.statusMessage.emit("Conversion completed!")
            self.statusMessage.emit(f"Would save here:{self.xml_files_path}")

        except Exception as e:
            # 在这里捕获异常并通过信号发送错误信息
            self.statusMessage.emit(f"Conversion failed: {str(e)} ")


class FormatXmlExcelThread(QThread):
    statusMessage = pyqtSignal(str)

    def __init__(self, file_path):
        super().__init__()
        self.file_path = file_path

    def add_limit_data(self):
        pass

    def run(self):
        try:
            # self.statusMessage.emit(f"开始chacc")
            # mark_failed_cells(self.file_path)
            workbook = openpyxl.load_workbook(self.file_path)
            sheet = workbook.get_sheet_by_name('FailData')

            yellow_fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            red_font = openpyxl.styles.Font(color='FF0000')
            default_font = openpyxl.styles.Font(color='000000')
            for row in sheet.iter_rows():
                for cell in row:
                    if str(cell.value).lower() == 'failed' or str(cell.value).lower() == 'fail' or str(
                            cell.value).lower() == 'FAIL':
                        cell.fill = yellow_fill
                        cell.font = red_font
                    else:
                        cell.font = default_font

            workbook.save(self.file_path)


        except Exception as e:
            self.statusMessage.emit(f"Failed to format xml file: {str(e)}")


class GetXmlData:
    def __init__(self, xml_file_path):
        self.xml_file_path = xml_file_path
        self.tree = ET.parse(xml_file_path)
        self.root = self.tree.getroot()
        self.directery = {}

    def get_serial_number(self):
        try:
            return self.root.attrib['Serial'] if 'Serial' in self.root.attrib else ""
        except Exception as e:
            print(f"Error getting serial number: {e}")
            return ""

    def get_all_param(self):
        try:
            pass
        except Exception as e:
            print(f"Error getting serial number: {e}")
            return ""

    def get_test_name_data(self):
        try:
            read_result = []
            if self.root:
                for element in self.root:
                    read_result.append(element.attrib['Id'] + element.attrib['Description'])
                return read_result
            else:
                return []
        except Exception as e:
            print(f"Error getting test name data: {e}")
            return []

    def get_status(self):
        try:
            return self.root.attrib['Status'] if 'Status' in self.root.attrib else ""
        except Exception as e:
            print(f"Error getting status: {e}")
            return ""

    def get_min_result_max_data(self):
        result = []
        for element in self.root:
            try:
                lsl = float(element.attrib['LSL'])
                usl = float(element.attrib['USL'])
                if element.attrib['Value'] == "∞":  # 如果值为无限大，则将状态标记为“失败”
                    status = 'failed'
                    value = element.attrib['Value']
                else:

                    value = float(element.attrib['Value'])
                    status = 'Passed' if lsl <= value <= usl else 'failed'
                result.extend([status, lsl, value, usl])


            except KeyError as e:
                print(f"Missing attribute in XML element: {e}")

        return result

    def dict_to_data(self, test_dict):
        for element in self.root:
            try:
                lsl = float(element.attrib['LSL'])
                usl = float(element.attrib['USL'])
                name = element.attrib['Id'] + element.attrib['Description']
                if element.attrib['Value'] == "∞":  # 如果值为无限大，则将状态标记为“失败”
                    status = 'failed'
                    value = element.attrib['Value']
                else:
                    value = float(element.attrib['Value'])
                    if lsl <= value <= usl:
                        status = 'Passed'
                    else:
                        status = 'failed'

                if name in test_dict:
                    test_dict[name]['states'] = status
                    test_dict[name]['Result'] = value
                    test_dict[name]['Min'] = lsl
                    test_dict[name]['Max'] = usl
            except Exception as e:
                print(e)

    def add_limit_data(self):
        try:
            if self.get_test_name_data():
                test_names = self.get_test_name_data()
                read_result = []
                for name in test_names:
                    read_result.extend([name, 'Min', 'Result', 'Max'])
                return read_result
        except Exception as e:
            print(f"Error adding limit data: {e}")
            return []

    def get_date_time(self):
        try:
            return self.root.attrib['Timestamp'] if 'Timestamp' in self.root.attrib else ""
        except Exception as e:
            print(f"Error getting serial number: {e}")
            return ""

    def get_testplan_number(self):
        try:
            return self.root.attrib['NestId'] if 'NestId' in self.root.attrib else ""
        except Exception as e:
            print(f"Error getting serial number: {e}")
            return ""


def mark_failed_cells(xlsx_file):
    # 加载工作簿
    workbook = openpyxl.load_workbook(xlsx_file)
    sheet = workbook.active
    # 定义红色字体和黄色填充
    red_font = openpyxl.styles.Font(color='FF0000')
    yellow_fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    # 使用多线程标记单元格
    def mark_cell(row_idx, col_idx, cell):
        cell_value = cell.valu
        if cell_value == 'failed' or cell_value == 'Fail' or cell_value == 'FAIL':
            print('发现了')
            cell.font = red_font
            cell.fill = yellow_fill

    # 创建线程池并提交任务
    with concurrent.futures.ThreadPoolExecutor() as executor:
        for row_idx, row in enumerate(sheet.iter_rows(), start=1):
            for col_idx, cell in enumerate(row, start=1):
                executor.submit(mark_cell, row_idx, col_idx, cell)

    # 保存修改后的工作簿
    workbook.save(xlsx_file)


# mark_failed_cells('xmlToData.xlsx')

class S56ReportWorker(QThread):
    statusMessage = pyqtSignal(str)

    def __init__(self, S56_report_path):
        super().__init__()
        self.S56_report_path = S56_report_path
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = "Data"
        self.data_type_1 = ['产品编号', '检测日期', '检测时间', '最大值', '最小值/对应角度', '平均值', '测试结果']

    # Excel 中的第一行：列名
    def title_1(self):
        column_names = self.data_type_1
        for col_idx, col_name in enumerate(column_names, start=1):
            self.ws.cell(row=1, column=col_idx, value=col_name)
        logging.info('Write down the title')

    # 读取 txt 文件
    def read_txt_file(self, file_path):
        try:
            with open(file_path, 'r') as f:
                return f.readlines()
        except FileNotFoundError:
            logging.error(f"File not found: {file_path}")
            return None

    # 读取单个 TXT 的数据
    def read_TPS1_data(self, file_path):
        try:
            lines = self.read_txt_file(file_path)
            logging.info('Running read_Tps_data')
            self.statusMessage.emit("Running")
            logging.info(f'file name is : {file_path}')
            self.statusMessage.emit(f'file name is : {file_path}')
            valid_data = []
            if lines is not None:
                for line in range(1, len(lines)):
                    # self.statusMessage.emit(f'read line: {line}')
                    logging.info(f'read line: {line}')
                    if lines:
                        data = lines[line].split()
                        logging.info(f'Reading data : {data}')
                        has_number = any(char.isdigit() for char in data[0])
                        if has_number:
                            product_No = (data[0])
                            check_date = (data[1])
                            check_time = (data[2])
                            up_value = float(data[3])
                            low_value = float(data[4])
                            average_value = float(data[5])
                            # logging.info(
                            #     f'product_No, check_date, check_time, up_value, low_value, average_value are : {product_No, check_date, check_time, up_value, low_value, average_value}')

                            try:
                                if len(data) > 6:
                                    test_result = data[6]
                                    valid_data.append(
                                        [product_No, check_date, check_time, up_value, low_value, average_value,
                                         test_result])  # 写入内存中的 Excel

                                else:
                                    valid_data.append(
                                        [product_No, check_date, check_time, up_value, low_value,
                                         average_value])  # 写入内存中的 Excel
                            except Exception as e:
                                logging.error(f"Error reading file: {e}")
                                continue

                        else:
                            logging.warning(f"Invalid data format in line {line}: {data}")
                            # print(f"在文件 {file_path} 的第 {line} 行，产品编号为： {data[0]}，数据无效，无法读取。")
                # self.statusMessage.emit("转换完成")
                # self.statusMessage.emit(f"Would save here:{self.S56_report_path}")

            else:
                logging.error(f"Could not read file: {file_path}")

            return valid_data
        except Exception as e:
            logging.error(f"Error reading file: {e}")
            self.statusMessage.emit(f"转换失败:{e}")
            return []

    def process_file(self, file_name):
        file_path = os.path.join(r'M4REK', file_name)
        data = self.read_TPS1_data(file_path)
        for row_data in data:
            self.ws.append(row_data)

    def get_file_names(self, directory):
        return [f for f in os.listdir(directory) if f.endswith('.txt')]

    def FormatXmlExcelThread(self, file_path):
        try:
            workbook = openpyxl.load_workbook(file_path)
            sheet = workbook.active
            yellow_fill = openpyxl.styles.PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            red_font = openpyxl.styles.Font(color='FF0000')
            default_font = openpyxl.styles.Font(color='000000')
            logging.info(f'开始在Excel文件中寻找NG的数据：{file_path}')
            for row in sheet.iter_rows():
                for cell in row:
                    if str(cell.value) == 'NG':
                        cell.fill = yellow_fill
                        cell.font = red_font
                    else:
                        cell.font = default_font
            workbook.save(file_path)
        except Exception as e:
            logging.error(f"Error formatting XML file: {e}")

    def run(self):
        try:
            self.statusMessage.emit("Start to work")
            self.title_1()
            logging.info(f'There are {len(self.get_file_names(self.S56_report_path))} files in the folder.')
            self.statusMessage.emit(f'There are {len(self.get_file_names(self.S56_report_path))} files in the folder.')
            file_names = self.get_file_names(self.S56_report_path)
            threads = []
            for file_name in file_names:
                t = threading.Thread(target=self.process_file, args=(file_name,))
                threads.append(t)
                t.start()

            for t in threads:
                t.join()
            excel_name = f'S56_Data{time.strftime("%Y%m%d%H%M%S")}.xlsx'
            self.wb.save(os.path.join(self.S56_report_path, excel_name))
            self.statusMessage.emit("Pending,please wait a moment。。。。。")
            self.FormatXmlExcelThread(os.path.join(self.S56_report_path, excel_name))  # 查找NG 数据
            self.statusMessage.emit("Conversion completed!")


        except Exception as e:

            self.statusMessage.emit(f"Conversion failed due to {str(e)}")
            logging.error(f"Error running program: {e}")


def getWordInString(keyword, string):
    if keyword in string:
        string = string.replace(keyword, ' ')
        return string
    else:
        return string


def list_to_dict(lst):
    # 检查列表是否为空
    if not lst:
        raise ValueError("输入的列表不能为空")
    # 使用字典推导式创建字典，列表中的每个元素作为键，值为空格字符串
    return {item: ' ' for item in lst}


def get_test_name(file_path):
    try:
        with open(file_path, 'r') as file:
            lines = file.readlines()
            test_names = []
            found_testname = False
            for line in lines:
                if line.startswith('[Testname]'):
                    found_testname = True
                elif found_testname:
                    if len(line) >= 31:
                        test_names.append(line[:31].strip())
            return test_names
    except Exception as e:
        print(f"Error in get_test_name for file {file_path}: {e}")
        return []


def create_test_dict(name_lst):
    test_dict = {}
    name_dic = list_to_dict(name_lst)
    for itme in name_dic:
        test_name = itme
        test_dict[test_name] = {'states': None, 'Result': None, 'Min': None, 'Max': None}
    return test_dict


def fill_test_dict(file_path, test_dict):
    with open(file_path, 'r') as file:
        lines = file.readlines()
        # 找到标题行的索引
        header_index = None
        name_lst = get_test_name(file_path)
        for i, line in enumerate(lines):
            if '[Testname]' in line:
                header_index = i
                break
        if header_index is None:
            return
        for line in lines[21:]:
            parts = line.split()
            name = ''
            if len(line) >= 31:
                name = line[:31].strip()
            if len(parts) < 4:
                continue
            if 'passed' in parts:
                index = parts.index('passed')
            elif 'failed' in parts:
                index = parts.index('failed')
            if 'TD>30' in parts:
                _index = parts.index('TD>30')
                parts.pop(_index)
            if index != 0:
                state = parts[index]
                min_val = parts[index + 1]
                if 'DEC"' in parts:
                    max_val = parts[index + 5]
                    result = parts[index + 3]
                else:
                    max_val = parts[index + 3]
                    result = parts[index + 2]
            # fail_test_data.extend([state, min_val, result, max_val])
            # 填充字典的值
            if name in test_dict:
                test_dict[name]['states'] = state
                test_dict[name]['Result'] = result
                test_dict[name]['Min'] = min_val
                test_dict[name]['Max'] = max_val




    # 初始化字体
# 设置ICON图标的
def create_icon_with_letter(letter):
    from PyQt5.QtCore import Qt
    from PyQt5.QtGui import QIcon, QPixmap, QPainter, QFont, QColor
    # 初始化字体
    font = QFont()
    font.setPointSize(16)  # 调整字体大小

    # 创建一个临时的 QPainter 来计算文本宽度
    temp_pixmap = QPixmap(1, 1)
    temp_painter = QPainter(temp_pixmap)
    temp_painter.setFont(font)
    text_width = temp_painter.fontMetrics().width(letter)
    temp_painter.end()

    # 动态调整图标宽度，增加一些边距
    icon_width = text_width + 10
    icon_height = 16  # 调整图标高度

    # 创建一个空白的 QPixmap 对象
    pixmap = QPixmap(icon_width, icon_height)
    pixmap.fill(Qt.white)  # 填充白色背景

    # 创建 QPainter 对象，用于在 QPixmap 上绘制
    painter = QPainter(pixmap)
    # 启用抗锯齿功能，使字体边缘更平滑
    painter.setRenderHint(QPainter.Antialiasing)
    painter.setRenderHint(QPainter.TextAntialiasing)

    # 设置字体
    painter.setFont(font)

    # 设置画笔颜色
    painter.setPen(QColor(0, 255, 0))

    # 计算字母的绘制位置，使其居中
    x = (pixmap.width() - text_width) // 2
    y = (pixmap.height() + painter.fontMetrics().ascent() - painter.fontMetrics().descent()) // 2

    # 绘制字母
    painter.drawText(x, y, letter)

    # 结束绘制
    painter.end()

    # 将 QPixmap 转换为 QIcon 对象
    icon = QIcon(pixmap)
    return icon


if __name__ == '__main__':
    app = QApplication(sys.argv)
    style_sheet = """
               QWidget#qt_windows_titlebar {
                   background-color: black;
                   color: white;
               }
               """
    app.setStyleSheet(style_sheet)
    # 深色窗口
    dark(app)
    ex = MainWindow()
    ex.show()
    sys.exit(app.exec_())
